package cn.edu.sdjzu.xg.bysj.dao;


import cn.edu.sdjzu.xg.bysj.domain.*;
import cn.edu.sdjzu.xg.bysj.service.*;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public final class GraduateProjectDao {
    private static GraduateProjectDao graduateProjectDao = new GraduateProjectDao();


    private GraduateProjectDao() {
    }

    public static GraduateProjectDao getInstance() {
        return graduateProjectDao;
    }

    public static Collection<GraduateProject> findAll(List conditionList,
                                                      Pagination pagination,
                                                      Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的对象
        Collection<GraduateProject> graduateProject = new TreeSet<GraduateProject>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection,"GraduateProject");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM GraduateProject");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            graduateProject.add(new GraduateProject(resultSet.getInt("id"),
                    resultSet.getString("title"),
                    null,
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id")),
                    GraduateProjectTypeService.getInstance().find(resultSet.getInt("graduateProjectType_id")),
                    GraduateProjectStatusService.getInstance().find(resultSet.getInt("graduateProjectStatus_id")),
                    GraduateProjectSourceService.getInstance().find(resultSet.getInt("graduateProjectSource_id")),
                    GraduateProjectTruenessService.getInstance().find(resultSet.getInt("graduateProjectTrueness_id")),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                    null,
                    null
                    ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProject;
    }

    public static GraduateProject find(int id,Connection connection) throws SQLException {
        GraduateProject graduateProject =null;
        //创建sql语句
        String find_sql = "select * from GraduateProject where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()){
            graduateProject = new GraduateProject(resultSet.getInt("id"),
                    resultSet.getString("title"),
                    null,
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id")),
                    GraduateProjectTypeService.getInstance().find(resultSet.getInt("graduateProjectType_id")),
                    GraduateProjectStatusService.getInstance().find(resultSet.getInt("graduateProjectStatus_id")),
                    GraduateProjectSourceService.getInstance().find(resultSet.getInt("graduateProjectSource_id")),
                    GraduateProjectTruenessService.getInstance().find(resultSet.getInt("graduateProjectTrueness_id")),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                    null,
                    null
            );
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProject;
    }


    public static boolean add(GraduateProject graduateProject,
                              Connection connection) throws SQLException {
        //创建sql语句
        String addGPC_sql =
                "insert into GraduateProject (title,graduateProjectCategory_id,graduateProjectType_id, " +
                        "graduateProjectStatus_id,graduateProjectSource_id,graduateProjectTrueness_id,teacher_id,year) values " +
                        "(?,?,?,?,?,?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGPC_sql);
        //为预编译参数赋值
        pstmt.setString(1,graduateProject.getTitle());
        pstmt.setInt(2,graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(3,graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(4,graduateProject.getGraduateProjectStatus().getId());
        pstmt.setInt(5,graduateProject.getGraduateProjectSource().getId());
        pstmt.setInt(6,graduateProject.getGraduateProjectTrueness().getId());
        pstmt.setInt(7,graduateProject.getTeacher().getId());
        pstmt.setInt(8,graduateProject.getYear());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            JdbcHelper.close(null,pstmt);
            return true;

        } else
            JdbcHelper.close(null,pstmt);
        return false;
    }
}
